﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Text;

/// <summary>
///ZHCX 的摘要说明
/// </summary>
public class ZHCX
{
  public ZHCX()
  {
    //
    //TODO: 在此处添加构造函数逻辑
    //
  }

  // 生成综合查询表
  public static void CreateZHCX(string sKHID, string sRQ)
  {
    string sSql = "select cx_khbm from zhcx where cx_khbm='" + sKHID + "' and convert(varchar(6),rq,112)='"
      + sRQ.Substring(0, 4) + sRQ.Substring(5, 2) + "'";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    if (dr.Read())
    {
    }
    else
    {
      sSql = @"insert into zhcx(cx_khbm,cx_khmc,cx_gldw,cx_khjb,cx_ic,rq,cx_bs,cx_id)
select a.khid,a.khmc,b.dwmc,c.khjb,a.ic,@1,a.kh,@2 from jcxx_khxx a,jcxx_dw b,jcxx_khjb c
where a.dwid=b.dwid and a.khjbid=c.khjbid and a.khid=@0";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10) 
                                 , new SqlParameter("@2", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sKHID;
      cmdParams[1].Value = sRQ;
      cmdParams[2].Value = CommonUse.GetID("zhcx", "cx_id");
      int i = DbHelperSQL.ExecuteSql(sSql, cmdParams);
    }
    dr.Close();
  }

  //生成综合查询表与抄表数据有关部分
  public static void UpdateCB(string sKHID, string sRQ, string sID)
  {
    double[] dtYS = CBSJ.GetYS(sKHID, sRQ);
    //double[] dtCY = CBSJ.GetCY(sKHID, sRQ);
    double ljcy = Convert.ToDouble(CBSJ.GetLJCY(sKHID, sRQ));
    ljcy = ljcy > 0 ? 0 : ljcy;
    string sSQJY = GetSQJY(sKHID, sRQ);
    string sBQSJHJ = JFSJ.GetBQSJHJ(sKHID, sRQ);
    //string sBQJY = GetBQJY(sKHID, sRQ);
    string sBYBS = "0", sCBSJ = "";
    SqlDataReader dr = CBSJ.GetCBSJ(sID);
    if (dr.Read())
    {
      sBYBS = dr["bybs"].ToString();
      sCBSJ = dr["cbsj"].ToString();
      //sYSQL = dr["ysql"].ToString();
    }
    dr.Close();
    string sSql = @"update zhcx set cx_ysql=@2,cx_sr=@3,cx_jgjj=@4,CX_bc=@5,
CX_bybs=@6,CX_cbsj=@7,CX_sjsyql=@8,CX_sybs=@9,CX_SLLJCY=@10,CX_JELJCY=@11,cx_cbid=@12,CX_SQJY=@13,CX_BQJY=@14
where cx_khbm=@0 and convert(varchar(6),rq,112)=@1";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10),
                               new SqlParameter("@2", SqlDbType.VarChar, 20), new SqlParameter("@3", SqlDbType.VarChar, 20),
                               new SqlParameter("@4", SqlDbType.VarChar, 20), new SqlParameter("@5", SqlDbType.VarChar, 20),
                               new SqlParameter("@6", SqlDbType.VarChar, 20), new SqlParameter("@7", SqlDbType.VarChar, 20),
                               new SqlParameter("@8", SqlDbType.VarChar, 20), new SqlParameter("@9", SqlDbType.VarChar, 20),
                               new SqlParameter("@10", SqlDbType.VarChar, 20), new SqlParameter("@11", SqlDbType.VarChar, 20),
                               new SqlParameter("@12", SqlDbType.VarChar, 20),new SqlParameter("@13", SqlDbType.VarChar, 20),
                               new SqlParameter("@14", SqlDbType.VarChar, 20)};
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
    cmdParams[2].Value = dtYS[0].ToString();
    cmdParams[3].Value = dtYS[1].ToString();
    cmdParams[4].Value = dtYS[2].ToString();
    cmdParams[5].Value = dtYS[3].ToString();
    cmdParams[6].Value = sBYBS;
    cmdParams[7].Value = sCBSJ;
    cmdParams[8].Value = dtYS[0].ToString();
    cmdParams[9].Value = ZHCX.GetSYBS(sKHID, sRQ);
    cmdParams[10].Value = 0;
    cmdParams[11].Value = ljcy;
    cmdParams[12].Value = sID;
    cmdParams[13].Value = sSQJY;
    cmdParams[14].Value = Math.Round((Convert.ToDouble(sSQJY) + dtYS[3] * -1 + Convert.ToDouble(sBQSJHJ)), 2) < 0 ? 0 :
      Math.Round((Convert.ToDouble(sSQJY) + dtYS[3] * -1 + Convert.ToDouble(sBQSJHJ)), 2);
    int i = DbHelperSQL.ExecuteSql(sSql, cmdParams);

    sSql = "update jcxx_khxx set zhye=@0 where khid=@1";
    cmdParams = new SqlParameter[2] { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = Math.Round((Convert.ToDouble(sSQJY) + dtYS[3] * -1 + Convert.ToDouble(sBQSJHJ)), 2) < 0 ? 0 :
      Math.Round((Convert.ToDouble(sSQJY) + dtYS[3] * -1 + Convert.ToDouble(sBQSJHJ)), 2);
    cmdParams[1].Value = sKHID;
    i = DbHelperSQL.ExecuteSql(sSql, cmdParams);

  }
  public static void UpdateCB_jgtz(string sKHID, string sRQ, string sID)
  {
    double[] dtYS = CBSJ.GetYS(sKHID, sRQ);
    //double[] dtCY = CBSJ.GetCY(sKHID, sRQ);
    double ljcy = Convert.ToDouble(CBSJ.GetLJCY(sKHID, sRQ));
    ljcy = ljcy > 0 ? 0 : ljcy;
    string sSQJY = GetSQJY(sKHID, sRQ);
    string sBQSJHJ = JFSJ.GetBQSJHJ(sKHID, sRQ);
    //string sBQJY = GetBQJY(sKHID, sRQ);
    string sBYBS = "0", sCBSJ = "";
    SqlDataReader dr = CBSJ.GetCBSJ(sID);
    if (dr.Read())
    {
      sBYBS = dr["bybs"].ToString();
      sCBSJ = dr["cbsj"].ToString();
      //sYSQL = dr["ysql"].ToString();
    }
    dr.Close();
    string sSql = @"update zhcx set cx_ysql=@2,cx_sr=@3,cx_jgjj=@4,CX_bc=@5,
CX_bybs=@6,CX_cbsj=@7,CX_sjsyql=@8,CX_sybs=@9,CX_SLLJCY=@10,CX_JELJCY=@11,cx_cbid=@12,CX_SQJY=@13,CX_BQJY=@14
where cx_khbm=@0 and convert(varchar(6),rq,112)=@1";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10),
                               new SqlParameter("@2", SqlDbType.VarChar, 20), new SqlParameter("@3", SqlDbType.VarChar, 20),
                               new SqlParameter("@4", SqlDbType.VarChar, 20), new SqlParameter("@5", SqlDbType.VarChar, 20),
                               new SqlParameter("@6", SqlDbType.VarChar, 20), new SqlParameter("@7", SqlDbType.VarChar, 20),
                               new SqlParameter("@8", SqlDbType.VarChar, 20), new SqlParameter("@9", SqlDbType.VarChar, 20),
                               new SqlParameter("@10", SqlDbType.VarChar, 20), new SqlParameter("@11", SqlDbType.VarChar, 20),
                               new SqlParameter("@12", SqlDbType.VarChar, 20),new SqlParameter("@13", SqlDbType.VarChar, 20),
                               new SqlParameter("@14", SqlDbType.VarChar, 20)};
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
    cmdParams[2].Value = dtYS[0].ToString();
    cmdParams[3].Value = dtYS[1].ToString();
    cmdParams[4].Value = dtYS[2].ToString();
    cmdParams[5].Value = dtYS[3].ToString();
    cmdParams[6].Value = sBYBS;
    cmdParams[7].Value = sCBSJ;
    cmdParams[8].Value = dtYS[0].ToString();
    cmdParams[9].Value = ZHCX.GetSYBS(sKHID, sRQ);
    cmdParams[10].Value = 0;
    cmdParams[11].Value = ljcy;
    cmdParams[12].Value = sID;
    cmdParams[13].Value = sSQJY;
    cmdParams[14].Value = Math.Round((Convert.ToDouble(sSQJY) + dtYS[3] * -1 + Convert.ToDouble(sBQSJHJ)), 2) < 0 ? 0 :
      Math.Round((Convert.ToDouble(sSQJY) + dtYS[3] * -1 + Convert.ToDouble(sBQSJHJ)), 2);
    int i = DbHelperSQL.ExecuteSql(sSql, cmdParams);

    sSql = "update jcxx_khxx set zhye=@0 where khid=@1";
    cmdParams = new SqlParameter[2] { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = Math.Round((Convert.ToDouble(sSQJY) + dtYS[3] * -1 + Convert.ToDouble(sBQSJHJ)), 2) < 0 ? 0 :
      Math.Round((Convert.ToDouble(sSQJY) + dtYS[3] * -1 + Convert.ToDouble(sBQSJHJ)), 2);
    cmdParams[1].Value = sKHID;
    i = DbHelperSQL.ExecuteSql(sSql, cmdParams);
  }

  //上月表数
  protected static string GetSYBS(string sKHID, string sRQ)
  {
    string s = "0";
    string sSql = @"select a.* from zhcx a
where convert(varchar(6),dateadd(month,1,a.rq),112)=@1 and a.cx_khbm=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.Read())
    {
      s = string.IsNullOrEmpty(dr["cx_bybs"].ToString()) ? "0" : dr["cx_bybs"].ToString();
    }
    dr.Close();
    return s;
  }

  //上期结余
  protected static string GetSQJY(string sKHID, string sRQ)
  {
    string s = "0";
    string sSql = @"select a.* from zhcx a
where  convert(varchar(6),dateadd(month,1,a.rq),112)=@1 and a.cx_khbm=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.Read())
    {
      s = string.IsNullOrEmpty(dr["cx_bqjy"].ToString()) ? "0" : dr["cx_bqjy"].ToString();
    }
    dr.Close();
    return s;
  }

  //本期结余
  protected static string GetBQJY(string sKHID, string sRQ)
  {
    string s = "0";
    string sSql = @"select a.* from zhcx a
where convert(varchar(6),a.rq,112)=@1 and a.cx_khbm=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.Read())
    {
      s = string.IsNullOrEmpty(dr["cx_bqjy"].ToString()) ? "0" : dr["cx_bqjy"].ToString();
    }
    dr.Close();
    return s;
  }

  //生成综合查询表与缴费数据有关部分
  public static void UpdateJF(string sKHID, string sRQ)
  {
    double[] dtYS = CBSJ.GetYS(sKHID, sRQ);
    //double[] dtSS = JFSJ.GetSS(sKHID, sRQ);
    //double[] dtCY = CBSJ.GetCY(sKHID, sRQ);
    double ljcy = Convert.ToDouble(CBSJ.GetLJCY(sKHID, sRQ));
    ljcy = ljcy > 0 ? 0 : ljcy;
    string sBQSJHJ = JFSJ.GetBQSJHJ(sKHID, sRQ);
    string sSQJY = GetSQJY(sKHID, sRQ);
    string sBCJY = KHXX.GetYE(sKHID);
    string sSql = @"update zhcx set cx_ssql=@2,cx_sjsr=@3,cx_sjjgjj=cast(@5 as float)-cast(@3 as float),CX_XJ=@5,
CX_BQSJ=@6,CX_SQJY=@7,CX_BQJY=@8,
CX_SLLJCY=@9,CX_JELJCY=@10
where cx_khbm=@0 and convert(varchar(6),rq,112)=@1";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10),
                               new SqlParameter("@2", SqlDbType.VarChar, 20), new SqlParameter("@3", SqlDbType.VarChar, 20),
                               new SqlParameter("@4", SqlDbType.VarChar, 20), new SqlParameter("@5", SqlDbType.VarChar, 20),
                               new SqlParameter("@6", SqlDbType.VarChar, 20), new SqlParameter("@7", SqlDbType.VarChar, 20),
                               new SqlParameter("@8", SqlDbType.VarChar, 20), new SqlParameter("@9", SqlDbType.VarChar, 20),
                               new SqlParameter("@10", SqlDbType.VarChar, 20)};
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
    cmdParams[2].Value = "0";
    cmdParams[3].Value = Convert.ToDouble(sBQSJHJ) >= dtYS[1] ? dtYS[1].ToString() : sBQSJHJ;
    cmdParams[4].Value = "0";
    cmdParams[5].Value = Convert.ToDouble(sBQSJHJ) >= dtYS[3] ? dtYS[3].ToString() : sBQSJHJ;
    cmdParams[6].Value = sBQSJHJ;
    cmdParams[7].Value = sSQJY;
    cmdParams[8].Value = Convert.ToDouble(sBCJY) < 0 ? 0 : Convert.ToDouble(sBCJY);
    cmdParams[9].Value = 0;
    cmdParams[10].Value = ljcy;
    int i = DbHelperSQL.ExecuteSql(sSql, cmdParams);

  }

  //获取数据
  public static DataSet GetDS(string date1, string date2)
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append("select ROW_NUMBER() OVER (ORDER BY cx_id desc) AS xh,a.*,a.CX_JELJCY*-1 JELJCY from zhcx a where a.rq>=@0 and a.rq<=cast(@1 as datetime) ");
    //sbSql.Append("order by cx_id desc");
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10) };
    cmdParams[0].Value = date1;
    cmdParams[1].Value = date2;
    DataSet ds = DbHelperSQL.Query(sbSql.ToString(), cmdParams);
    return ds;
  }

  //获取抄表与收费合并
  public static DataSet GetCBSF(string khid, string nf)
  {
    string sql = @"
select cbsj,sum(bqql) ysql,sum(ysfy) ysfy,null ssfy,null bcjyk from sjlr_cbsj a,sjlr_bqql b
 where a.cbsjid=b.cbsjid and khid=@0 and bqql_sfsb=2 and convert(varchar(4),a.cbsj,112)=@1 group by cbsj
union all
select jfsj,null,null,bqsj,bcjyk from sjlr_jfsj where khid=@0 and convert(varchar(4),jfsj,112)=@1
order by cbsj";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = khid;
    cmdParams[1].Value = nf;
    DataSet ds = DbHelperSQL.Query(sql, cmdParams);
    double snjz = Convert.ToDouble(JFSJ.GetSNJZ(khid, nf));
    double bcjy = 0, ssje = 0, ysje = 0;
    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
      if (i == 0)
      {
        ssje = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["ssfy"].ToString()) ? 0 : Convert.ToDouble(ds.Tables[0].Rows[i]["ssfy"].ToString());
        ysje = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["ysfy"].ToString()) ? 0 : Convert.ToDouble(ds.Tables[0].Rows[i]["ysfy"].ToString());
        bcjy = Math.Round((ssje - ysje), 2);
        ds.Tables[0].Rows[i]["bcjyk"] = Math.Round((snjz + bcjy), 2).ToString();
      }
      else
      {
        snjz = Convert.ToDouble(ds.Tables[0].Rows[i - 1]["bcjyk"].ToString());
        ssje = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["ssfy"].ToString()) ? 0 : Convert.ToDouble(ds.Tables[0].Rows[i]["ssfy"].ToString());
        ysje = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["ysfy"].ToString()) ? 0 : Convert.ToDouble(ds.Tables[0].Rows[i]["ysfy"].ToString());
        bcjy = Math.Round((ssje - ysje), 2);
        ds.Tables[0].Rows[i]["bcjyk"] = Math.Round((snjz + bcjy), 2).ToString();
      }
    }
    return ds;
  }
}
